#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
SQOOP_HOME=/usr/bin/

$HIVE_CMD -S -f "/root/Liam/online_edu/交付文件/create_test.sql"


${HIVE_HOME} -S -e "
create database if not exists edu_dwd;
use edu_dwd;
-- 建表
-- DWD_事实表：
drop table if exists edu_dwd.fact_customer_relationship;
create table edu_dwd.fact_customer_relationship
(
    -- 指标
    id                          int         comment '意向id',

    -- 维度
    create_date_time            string      comment '意向构建时间',
    update_date_time            string      comment '最后更新时间',
    deleted                     int         comment '是否被删除（禁用）',
    customer_id                 int         comment '所属客户id',
    origin_type                 string      comment '线上线下数据：来源线上（NETSERVICE or PRESIGNUP）、其他线下',
    itcast_school_id            int         comment '校区Id',
    itcast_subject_id           int         comment '学科Id',
    creator                     int         comment '创建人',
    origin_channel              string      comment '来源渠道'
)
comment '客户意向事实表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;

drop table if exists edu_dwd.fact_customer_clue;
create table edu_dwd.fact_customer_clue
(
    -- 指标
    id                           int            comment '线索id',

    -- 维度
    customer_state               string         comment '线索状态(NEW_CUSTOMER：新学员,OLD_CUSTOMER：老学员)',
    customer_relationship_id     int            comment '客户关系id'
    )
comment '客户线索事实表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;



-- DWD_维度表：
drop table if exists edu_dwd.dim_customer;
create table edu_dwd.dim_customer
(
    id                       int            comment '学员id' ,
    area                     string         comment '所在区域'
)
comment '学员地区维度信息表'
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;

drop table if exists edu_dwd.dim_itcast_school;
create table edu_dwd.dim_itcast_school
(
    id               int      comment '校区id',
    name             string   comment '校区名称'
)
comment '校区信息维度表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;

drop table if exists edu_dwd.dim_itcast_subject;
create table edu_dwd.dim_itcast_subject
(
    id               int      comment '学科id',
    name             string   comment '学科名称'
)
comment '学科信息维度表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;

drop table if exists edu_dwd.dim_employee;
create table edu_dwd.dim_employee
(
    id                  int                                       comment '员工id',
    scrm_department_id  int                                       comment 'SCRM内部部门id'
)
comment '员工部门关系表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;

drop table if exists edu_dwd.dim_scrm_department;
create table edu_dwd.dim_scrm_department
(
    `id`               int      comment '部门id',
    `name`             string   comment '部门名称'
)
comment '员工部门维度表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;

drop table if exists edu_dwd.dim_customer_clue;
create table edu_dwd.dim_customer_clue
(
    customer_relationship_id int                                     comment '客户关系id',
    customer_state           string                                  comment '线索状态(NEW_CUSTOMER：新学员,OLD_CUSTOMER：老学员)'
)
comment '客户线索维度表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;

drop table if exists edu_dwd.dim_customer_appeal;
create table edu_dwd.dim_customer_appeal
(
    customer_relationship_first_id int                comment '第一条客户关系id',
    appeal_status                  int                comment '申诉状态，0:待稽核 1:无效 2：有效'
)
comment '线索申诉信息维度表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;

drop table if exists edu_dwd.dim_customer_relationship;
create table edu_dwd.dim_customer_relationship
(
    id                          int         comment '意向id',
    create_date_time            string      comment '创建时间',
    origin_type                 string      comment '数据来源:线上（NETSERVICE or PRESIGNUP）、其他线下'
)
comment '客户意向维度表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;


-- 插入数据

insert into edu_dwd.dim_customer
select id, area
from edu_ods.customer;

insert into edu_dwd.dim_customer_appeal
select customer_relationship_first_id, appeal_status
from edu_ods.customer_appeal
where appeal_status != 1;

insert into edu_dwd.dim_customer_clue
select customer_relationship_id,
       case clue_state when 'VALID_NEW_CLUES' then 'NEW_CUSTOMER'
                       when 'INVALID_PUBLIC_OLD_CLUE' then 'OLD_CUSTOMER'
        else 'other' end as customer_state
from edu_ods.customer_clue
where deleted = false;

insert into edu_dwd.dim_customer_relationship
select id,
       create_date_time,
       case when origin_type = 'NETSERVICE' or origin_type = 'PRESIGNUP' then '线上'
            else '线下' end as origin_type
from edu_ods.customer_relationship;

insert into edu_dwd.dim_employee
select id, scrm_department_id
from edu_ods.employee;

insert into edu_dwd.dim_itcast_school
select id, name
from edu_ods.itcast_school;

insert into edu_dwd.dim_itcast_subject
select id, name
from edu_ods.itcast_subject;

insert into edu_dwd.dim_scrm_department
select id, name
from edu_ods.scrm_department;

insert into edu_dwd.fact_customer_clue
select  id
       ,case clue_state when 'VALID_NEW_CLUES' then 'NEW_CUSTOMER'
                       when 'INVALID_PUBLIC_OLD_CLUE' then 'OLD_CUSTOMER'
        else 'other' end as customer_state
       , customer_relationship_id
from edu_ods.customer_clue;

insert into edu_dwd.fact_customer_relationship
select
        id,
        create_date_time,
        update_date_time,
        deleted,
        customer_id,
        case when origin_type = 'NETSERVICE' or origin_type = 'PRESIGNUP' then '线上'
            else '线下' end as origin_type,
        itcast_school_id,
        itcast_subject_id,
        creator,
        origin_channel
from edu_ods.customer_relationship;
"